package com.xqc;

import com.alibaba.fastjson.JSON;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class XqcReadExcelUtils {
	
	private static  final Logger logger = LoggerFactory.getLogger(XqcReadFileUtils.class);
	
	public static void main(String[] args) {
		XqcReadFileUtils.readFile();
		readExcel();
	}
	
	private static void readExcel()  {
        try {
             //表名称
             String tableName = "t_abc_xax";
             Map<String, String> columnFieldType = getColumnFieldType(tableName);
			CreateEntityUtils.CreateEntity(columnFieldType,tableName,"daihuajun");
         } catch (Exception e) {
        	 logger.error("读取excel异常",e);
        }
	}
	
	/**
	 * 获取所有列字段
	 */
	private static List<String> columnTitles(XSSFRow xssfRow,int maxCell){ 
		List<String> columnTitles = new ArrayList<>();
		for (int i = 0; i < maxCell; i++) {
			columnTitles.add(xssfRow.getCell(i).getStringCellValue().toUpperCase());
		}
		return columnTitles;
	}
	
	/**
	 * 获取数据库字段类型
	 */
	private static Map<String,String> getColumnFieldType2(String sheetName) throws SQLException{
		Map<String,String> fieldMap = new HashMap<>();
		Connection conn = JdbcConnUtils.getConnection();
		String sql = "show full columns from "+sheetName;
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		for (int i = 1; i <= columnCount; i++) {
			fieldMap.put(metaData.getColumnName(i), metaData.getColumnClassName(i));
		}
		JdbcCloseUtils.closeAll(conn, ps, rs);
		return fieldMap;
	}

	/**
	 * 获取数据库字段类型
	 */
	private static Map<String,String> getColumnFieldType(String sheetName) throws SQLException{
		Map<String,String> fieldMap = new HashMap<>();
		Connection conn = JdbcConnUtils.getConnection();
		String sql = "show full columns from "+sheetName;
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			System.out.println(rs.getString("Field")+"\t"+rs.getString("Comment"));
		}
		ResultSetMetaData metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		for (int i = 1; i <= columnCount; i++) {
			fieldMap.put(metaData.getColumnName(i), metaData.getColumnClassName(i));
		}
		JdbcCloseUtils.closeAll(conn, ps, rs);
		getTables(sheetName);
		return fieldMap;
	}

	/**
	 * 获取数据库字段类型
	 */
	private static Map<String,String> getTables(String sheetName) throws SQLException{
		Map<String,String> fieldMap = new HashMap<>();
		Connection conn = JdbcConnUtils.getConnection();
		String sql = "show full columns from "+sheetName;
		DatabaseMetaData metaData = conn.getMetaData();
		ResultSet rs = metaData.getTables(null, null, null, new String[]{"TABLE"});
//		PreparedStatement ps = conn.prepareStatement(sql);
//		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			System.out.println("===============");
			//获取表名称
			System.out.println(rs.getString(3)+"\t"+rs.getString(2));
//			System.out.println(JSON.toJSONString(rs.get));
		}
		JdbcCloseUtils.closeAll(conn, null, rs);
		return fieldMap;
	}

}
